﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApplication1.Common;
using FanjiaHotelPlatform.utils.Kits;
using Maticsoft.DBUtility;

namespace WindowsFormsApplication1
{
    public partial class HotelDealTool : Form
    {
        public HotelDealTool()
        {
            InitializeComponent();
        }

        private void HotelDealTool_Load(object sender, EventArgs e)
        {
            //var s = "钻石会员8.5折";
            //var tag = s.Substring(0, s.IndexOf("会员"));
            //tag = $"美团{tag}会员";

            //MessageBox.Show(tag);

            var g = Guid.Parse("B8E0D4A6-AB99-444C-9DD8-75D1EDDDDDDD");
            //HuaZhuMerge();
            //RptHotelMergeByCity(Guid.Parse("F9D2DD88-B2E6-478D-9DD8-907E0B85AA1A"));

            //HotelMergeDeal("华住");

            return;
            //MeituanMerge();

            //HotelMergeDeal("美团");
            return;

            DataGridViewCheckBoxColumn dtCheck = new DataGridViewCheckBoxColumn();
            dtCheck.HeaderText = "";
            dataGridView1.Columns.Add(dtCheck);

            var dtHotelCheckSim = DbHelperMySQL
                .Query($"SELECT * FROM hotelchecksim LIMIT 100", "MysqlConn").Tables[0];

            this.dataGridView1.DataSource = dtHotelCheckSim;

        }

        private void button1_Click(object sender, EventArgs e)
        {
            //MergeByCity();
            //string remarkid = "";
            //for (int i = 0; i < dataGridView1.Rows.Count; i++)
            //{
            //    DataGridViewRow row = dataGridView1.Rows[i];
            //    if (row.Cells[0].Value != null)
            //    {
            //        if (bool.Parse(row.Cells[0].Value.ToString()) == true)
            //        {
            //            int row1 = row.Index;
            //            remarkid = remarkid + "" + this.dataGridView1[1, row1].Value.ToString() + "" + ",";
            //        }
            //    }
            //}
            //if (remarkid.Length > 0)
            //{
            //    remarkid = remarkid.Substring(0, remarkid.Length - 1);
            //    MessageBox.Show(remarkid);
            //}
        }

        private void MergeByCity()
        {
            var dtProv = DbHelperSQL.Query("SELECT Id,Name FROM Provinces").Tables[0];

            if (dtProv != null && dtProv.Rows.Count > 0)
            {
                foreach (DataRow drProv in dtProv.Rows)
                {
                    var dtCity = DbHelperSQL.Query($"SELECT Id,Name FROM Cities WHERE Isdeleted =0 AND ProvinceId='{drProv["Id"].ToString()}'").Tables[0];

                    if (dtCity != null && dtCity.Rows.Count > 0)
                    {
                        foreach (DataRow drCity in dtCity.Rows)
                        {
                            //var cityId = Guid.Parse(drCity["Id"].ToString());
                            //RptHotelMergeByCity(cityId);
                            //HotelMergeDeal(drCity["Name"].ToString());
                        }
                    }
                }
            }
        }

        private void MeituanMerge()
        {
            try
            {
                //查询mapping表多供应商映射
                var mappingDt = DbHelperSQL
                    .Query(@"SELECT HotelId,SupplierHotelId from [HotelMappings] WHERE supplierid='B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC' and IsDeleted = 0 AND  SupplierHotelId in(
                    SELECT SupplierHotelId from[dbo].[HotelMappings]
                    WHERE supplierid = 'B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC' and IsDeleted = 0
                    GROUP BY SupplierHotelId having count(1) > 1)").Tables[0];

                if (mappingDt.Rows.Count > 0)
                {
                    var mergeDic = mappingDt.AsEnumerable().GroupBy(h => h["SupplierHotelId"].ToString())
                        .ToDictionary(h => h.Key, h => h.ToList());

                    if (mergeDic.Count > 0)
                    {
                        var timeNow = DateTime.Now;
                        var sDt = new DataTable();
                        sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                        sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                        sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                        foreach (var item in mergeDic)
                        {
                            var groupId = Guid.NewGuid();
                            var mappingList = item.Value;
                            mappingList.ForEach(m =>
                            {
                                var dr = sDt.NewRow();
                                dr["HotelId"] = Guid.Parse(m["HotelId"].ToString());
                                dr["GroupId"] = groupId;
                                dr["CreationTime"] = timeNow;

                                sDt.Rows.Add(dr);
                            });
                        }

                        DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                        var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");

                        if (s)
                        {
                            MessageBox.Show("OK!");
                        }
                        else
                        {
                            MessageBox.Show("ERROR!");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                LogHelper.logError.Error(e);
            }
        }

        private void HotelMergeDeal(string cityName)
        {
            try
            {
                var supplierOrderList = new List<(string, string)>()
                {
                    ("89E38E71-47D3-4946-92F4-B41E55ECD38F", "华住直连"),
                    ("607B69C3-2DBA-41B5-BCAF-10A014FAF1DD", "畅联-亚朵"),
                    //("B6C0D180-D96F-4FCB-A8CF-A16DA3DD987F", "畅联-维也纳"),
                    ("073A26B3-874C-4903-B787-6465EE23789B", "如家"),
                    ("0C1C1CDC-C831-4ECF-B362-A47F0E1AC529", "锦江-铂涛直连"),
                    ("D1D0C7F2-DC98-4B64-B36B-F6CD9E7FBB08", "畅联-格林"),
                    ("CE833C6E-D06F-46A3-B53E-204E91E83AB4", "畅联-君亭"),
                    ("557C7AB5-5816-4C1F-8AED-339A96177143", "东呈直连"),
                    ("118F9754-0B86-47CB-AAC2-FC75220D90A6", "畅联-都市118"),
                    ("B8E0D4A6-AB99-444C-B1CB-75D1EA062EFC", "美团直连"),
                    ("798BD348-9911-4DB4-92CA-848BB0064EC0", "千淘科技"),
                    ("A8375567-B63C-1B9D-F2BC-AA96D2605F53", "丽呈直连"),
                    ("1D2E9BDC-FBE2-40BE-8AB4-D28CDC811517", "艺龙直连"),
                    ("61FDCC1D-888B-4A53-98ED-40AC18A156CB", "途牛笛风"),
                    ("24924AF6-48D4-4B7A-BA81-103DA6084C69", "四海度假"),
                    ("A7685C06-629D-454B-98DA-81F4E6DE1433", "携程直连"),
                    ("CC7F30A4-1674-32C7-FB2F-0E8D368E2126", "道旅"),
                    ("00000000-0000-0000-0000-000000000000", "自营"),
                    ("55336424-C7DF-4453-B2F5-72AE8FD0D177", "HotelBeds"),
                    ("606DD41F-6E52-4B28-A50A-543F542BB972", "逍遥行"),
                    ("138E28A8-17AC-47BD-AD2F-4C032CC1F994", "神兽国旅")
                };

                var conStr = "";//" WHERE GroupId='55BD82F3-D4E9-4A84-A3E3-BC0DC77C027C'";

                var strSql = @"SELECT [HotelId]
                            ,[GroupId]
                            , b.SupplierId
                        FROM[HotelMergeGroup] g
                            left join[dbo].[HotelBasicInfoes] b on g.HotelId = b.id" + conStr;

                var dt = DbHelperSQL.Query(strSql);

                if (dt?.Tables[0] != null && dt.Tables[0].Rows.Count > 0)
                {
                    //按分组组合出待合并酒店
                    Dictionary<Guid, List<HotelItem>> dic = new Dictionary<Guid, List<HotelItem>>();

                    foreach (DataRow dr in dt.Tables[0].Rows)
                    {
                        var groupId = Guid.Parse(dr["GroupId"].ToString());

                        Guid.TryParse(dr["SupplierId"].ToString(), out var supplierId);

                        if (dic.ContainsKey(groupId))
                        {
                            var list = dic[groupId];
                            list.Add(new HotelItem
                            {
                                HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                SupplierId = supplierId
                            });
                        }
                        else
                        {
                            dic.Add(groupId, new List<HotelItem>()
                            {
                                new HotelItem
                                {
                                    HotelId = Guid.Parse(dr["HotelId"].ToString()),
                                    SupplierId = supplierId
                                }
                            });
                        }
                    }

                    //查询mapping表多供应商映射
                    var mappingDt = DbHelperSQL
                        .Query($"select * from HotelMappings where HotelId in(select hotelid from [HotelMergeGroup] {conStr}) and IsDeleted = 0")
                        .Tables[0];

                    if (mappingDt.Rows.Count > 0)
                    {
                        var mappingDic = mappingDt.AsEnumerable().GroupBy(h => Guid.Parse(h["HotelId"].ToString()))
                            .ToDictionary(h => h.Key, h => h.ToList());

                        var mappingSaveDt = mappingDt.Clone(); //整理好的映射，将替换成正式数据
                        var mappingRptDt = mappingDt.Clone(); //重复合并的映射，保存后备查

                        var delHotelDt = new DataTable();
                        delHotelDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));

                        foreach (var item in dic)
                        {
                            var hList = item.Value;
                            var supplierTmp = new List<Guid>();
                            var supplierTmpDic = new Dictionary<Guid, SupplierMappingInfo>();

                            //判断使用哪个酒店留底合并
                            var hotelId = Guid.Empty;
                            foreach (var i in supplierOrderList)
                            {
                                var h = hList.FirstOrDefault(m => m.SupplierId == Guid.Parse(i.Item1));
                                if (h != null)
                                {
                                    hotelId = h.HotelId;
                                    break;
                                }
                            }

                            //整理多供应商Mapping映射记录
                            foreach (var hotelItem in hList)
                            {
                                if (mappingDic.ContainsKey(hotelItem.HotelId))
                                {
                                    var mappingList = mappingDic[hotelItem.HotelId];

                                    if (hotelItem.HotelId == hotelId)
                                    {
                                        MappingMerge(mappingList, supplierTmpDic, mappingSaveDt, mappingRptDt);
                                    }
                                    else
                                    {
                                        MappingMerge(mappingList, supplierTmpDic, mappingSaveDt, mappingRptDt, hotelId);

                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                                else
                                {
                                    if (hotelItem.HotelId != hotelId)
                                    {
                                        var drN = delHotelDt.NewRow();
                                        drN["HotelId"] = hotelItem.HotelId;
                                        delHotelDt.Rows.Add(drN);
                                    }
                                }
                            }
                        }

                        //return;

                        //保存处理结果
                        if (mappingSaveDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMerge");
                            DbHelperSQL.SaveListDataBat(mappingSaveDt, "HotelMappingMerge");

                            LogHelper.WriteLog($"{cityName}-保存处理结果，共{mappingSaveDt.Rows.Count}");
                        }

                        if (mappingRptDt.Rows.Count > 0)
                        {
                            //DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeRpt");
                            DbHelperSQL.SaveListDataBat(mappingRptDt, "HotelMappingMergeRpt");
                            LogHelper.WriteLog($"{cityName}-mappingRptDt，共{mappingRptDt.Rows.Count}");
                        }

                        if (delHotelDt.Rows.Count > 0)
                        {
                            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMappingMergeDel");
                            DbHelperSQL.SaveListDataBat(delHotelDt, "HotelMappingMergeDel");
                            LogHelper.WriteLog($"{cityName}-需逻辑删除合并掉的酒店，共{delHotelDt.Rows.Count}");
                        }

                        //MessageBox.Show("完成!");
                        //return;

                        var sqlList = new List<string>();
                        //删除待处理的映射数据
                        sqlList.Add($"DELETE FROM HotelMappings WHERE HotelId in(SELECT hotelid FROM [HotelMergeGroup] {conStr})");

                        //插入整理后替换的映射结果
                        sqlList.Add(@"INSERT INTO [dbo].[HotelMappings]
                                       ([Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,[CreationUserName]
                                       ,[CreationTime]
                                       ,[LastModificationUserId]
                                       ,[LastModificationUserName]
                                       ,[LastModificationTime]
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual])
                                 SELECT
                                       [Id]
                                       ,[HotelId]
                                       ,[SupplierId]
                                       ,[SupplierHotelId]
                                       ,[IsActive]
                                       ,[CreationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[LastModificationUserId]
                                       ,'SysMerge'
                                       ,GETDATE()
                                       ,[IsDeleted]
                                       ,[DeleterUserId]
                                       ,[DeleterUserName]
                                       ,[DeletionTime]
                                       ,[Status]
                                       ,[IsOnline]
                                       ,[IsManual]
		                               FROM HotelMappingMerge");

                        //逻辑删除被合并掉的酒店
                        sqlList.Add("UPDATE [HotelBasicInfoes] SET IsDeleted=1,DeletionTime=GETDATE(),DeleterUserName='SysMerge',LastModificationTime=GETDATE() WHERE ID IN(SELECT HOTELID FROM HotelMappingMergeDel)");

                        DbHelperSQL.ExecuteSqlTran(sqlList);
                    }
                }

                //MessageBox.Show("完成!");
            }
            catch (Exception e)
            {
                //MessageBox.Show(e.Message);
                LogHelper.logError.Error(e);
            }
        }

        private static void MappingMerge(List<DataRow> mappingList, Dictionary<Guid, SupplierMappingInfo> supplierTmpDic, DataTable mappingSaveDt,
            DataTable mappingRptDt, Guid? hotelId = null)
        {
            mappingList.ForEach(m =>
            {
                var supplierHotelId = m["SupplierHotelId"].ToString();
                var creationTime = ConvertKit.ToDateTime(m["CreationTime"]);
                var isActive = ConvertKit.ToBoolean(m["IsActive"]);
                var mpSupplierId = Guid.Parse(m["SupplierId"].ToString());

                if (supplierTmpDic.ContainsKey(mpSupplierId)) //同一供应商存在多条映射关系
                {
                    bool isReplace = false;
                    var sMpInfo = supplierTmpDic[mpSupplierId];
                    if (supplierHotelId != sMpInfo.SupplierHotelId) //供应商酒店Id不同，取时较后的映射
                    {
                        if (isActive == sMpInfo.IsActive)
                        {
                            if (creationTime > sMpInfo.CreationTime)
                            {
                                isReplace = true;
                            }
                        }
                    }

                    if (isActive == true && sMpInfo.IsActive == false) //取激活状态的映射
                    {
                        isReplace = true;
                    }

                    if (isReplace) //替换换映射
                    {
                        var drSel = mappingSaveDt.Select($"Id='{sMpInfo.MapDataRow["Id"]}'");
                        if (drSel?.Length > 0)
                        {
                            mappingRptDt.Rows.Add(drSel[0].ItemArray);
                            mappingSaveDt.Rows.Remove(drSel[0]);
                        }

                        m["HotelId"] = sMpInfo.MapDataRow["HotelId"];
                        mappingSaveDt.Rows.Add(m.ItemArray);

                        sMpInfo.SupplierHotelId = supplierHotelId;
                        sMpInfo.IsActive = isActive;
                        sMpInfo.CreationTime = creationTime;
                        sMpInfo.MapDataRow = m;
                        //sMpInfo.MapRowId = Guid.Parse(m["Id"].ToString());
                    }
                    else
                    {
                        mappingRptDt.Rows.Add(m.ItemArray);
                    }
                }
                else
                {
                    if (hotelId != null)
                        m["HotelId"] = hotelId;

                    var sMpInfo = new SupplierMappingInfo
                    {
                        SupplierHotelId = supplierHotelId,
                        IsActive = isActive,
                        CreationTime = creationTime,
                        MapDataRow = m,
                        //MapRowId = Guid.Parse(m["Id"].ToString())
                };
                    supplierTmpDic.Add(mpSupplierId, sMpInfo);

                    mappingSaveDt.Rows.Add(m.ItemArray);
                }
            });
        }

        private void HuaZhuMerge()
        {
            try
            {
                //查询mapping表多供应商映射
                var mappingDt = DbHelperSQL
                    .Query(@"SELECT HotelId,SupplierHotelId from [HotelMappings] WHERE supplierid='89E38E71-47D3-4946-92F4-B41E55ECD38F' and IsDeleted = 0 AND  SupplierHotelId in(
                    SELECT SupplierHotelId from[dbo].[HotelMappings]
                    WHERE supplierid = '89E38E71-47D3-4946-92F4-B41E55ECD38F' and IsDeleted = 0
                    GROUP BY SupplierHotelId having count(1) > 1)").Tables[0];

                if (mappingDt.Rows.Count > 0)
                {
                    var mergeDic = mappingDt.AsEnumerable().GroupBy(h => h["SupplierHotelId"].ToString())
                        .ToDictionary(h => h.Key, h => h.ToList());

                    if (mergeDic.Count > 0)
                    {
                        var timeNow = DateTime.Now;
                        var sDt = new DataTable();
                        sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                        sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                        sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                        foreach (var item in mergeDic)
                        {
                            var groupId = Guid.NewGuid();
                            var mappingList = item.Value.Distinct()?.ToList();

                            if (mappingList.Count <= 1)
                                continue;

                            mappingList.ForEach(m =>
                            {
                                var dr = sDt.NewRow();
                                dr["HotelId"] = Guid.Parse(m["HotelId"].ToString());
                                dr["GroupId"] = groupId;
                                dr["CreationTime"] = timeNow;

                                sDt.Rows.Add(dr);
                            });
                        }

                        DbHelperSQL.ExecuteSql("INSERT INTO [HotelMergeGroup_BAK] SELECT * FROM [HotelMergeGroup]");
                        DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                        var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");

                        if (s)
                        {
                            MessageBox.Show("OK!");
                        }
                        else
                        {
                            MessageBox.Show("ERROR!");
                        }
                    }
                }

            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                LogHelper.logError.Error(e);
            }
        }

        private void RptHotelMergeByCity(Guid cityId)
        {
            try
            {
                var cityAllDt = DbHelperSQL
                    .Query($@"SELECT Id,Name FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0").Tables[0];

                var rptNameDt = DbHelperSQL
                    .Query($@"SELECT Name,count(1) Num FROM HotelBasicInfoes WHERE cityid='{cityId}' and isdeleted=0 group by name having count(1)>1")
                    .Tables[0];


                if (rptNameDt.Rows.Count > 0)
                {
                    var timeNow = DateTime.Now;
                    var sDt = new DataTable();
                    sDt.Columns.Add(new DataColumn("HotelId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("GroupId", typeof(Guid)));
                    sDt.Columns.Add(new DataColumn("CreationTime", typeof(DateTime)));

                    foreach (DataRow drName in rptNameDt.Rows)
                    {
                        var hotelName = drName["Name"].ToString();
                        var drRpt = cityAllDt.Select("Name='" + hotelName.Replace("'","''") + "'");

                        if (drRpt != null && drRpt.Length > 0)
                        {
                            var groupId = Guid.NewGuid();

                            drRpt.ToList().ForEach(m =>
                            {
                                var dr = sDt.NewRow();
                                dr["HotelId"] = Guid.Parse(m["Id"].ToString());
                                dr["GroupId"] = groupId;
                                dr["CreationTime"] = timeNow;

                                sDt.Rows.Add(dr);
                            });
                        }
                    }

                    DbHelperSQL.ExecuteSql("INSERT INTO [HotelMergeGroup_BAK] SELECT * FROM [HotelMergeGroup]");
                    DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelMergeGroup");

                    var s = DbHelperSQL.SaveListDataBatM(sDt, "HotelMergeGroup");

                    if (s)
                    {
                        //MessageBox.Show("OK!");
                    }
                    else
                    {
                        MessageBox.Show("ERROR!");
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
                LogHelper.logError.Error(e);
            }
        }
    }

    public class SupplierMappingInfo
    {
        public bool IsActive { get; set; }
        public string SupplierHotelId { get; set; }
        public DateTime CreationTime { get; set; }
        //public int RowIndex { get; set; }
        public DataRow MapDataRow { get; set; }
        public Guid MapRowId { get; set; }
    }
}
